package com.liu.db;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.liu.crypto.Aes;
import com.liu.keeppwd.SysState;
import com.liu.model.Ac;
import com.liu.model.AcComparator;
import com.liu.myerror.KeyEmptyError;

public class DbAc {

	static String[] zd = { "uid", "groupId", "title", "eMail", "jyUUID",
			"summary", "createTime", "updateTime", "status", "url" };
	static int[] type = { 1, 1, 1, 1, 1, 1, 2, 2, 2, 1 };

	public static String getDbCreateS() {
		String res = "create table ac(";
		res += "UUID char(32) primary key,";

		for (int i = 0; i < zd.length; i++) {
			res += zd[i];
			if (type[i] == 1) {
				res += " varchar(2000)";
			} else {
				res += " bigint";
			}
			if (i != zd.length - 1) {
				res += ",";
			}
		}
		res += ")";
		return res;
	}

	public static List<Object[]> AddRawData(Ac ac) {

		List<Object[]> res = new ArrayList<Object[]>();

		String res1 = "delete from ac where UUID=?";
		Object[] obj1 = new Object[2];
		obj1[0] = ac.getUUID();
		obj1[1] = res1;
		res.add(obj1);

		String res2 = "insert into ac(UUID,uid,groupId,title,eMail,jyUUID,summary,createTime,updateTime,status,url) values(?,?,?,?,?,?,?,?,?,?,?)";
		Object[] obj2 = new Object[12];
		obj2[0] = ac.getUUID();
		obj2[1] = ac.getUid();
		obj2[2] = ac.getGroupId();
		obj2[3] = ac.getTitle();
		obj2[4] = ac.geteMail();
		obj2[5] = ac.getJyUUID();
		obj2[6] = ac.getSummary();
		obj2[7] = ac.getCreateTime().getTime();
		obj2[8] = ac.getUpdateTime().getTime();
		obj2[9] = ac.getStatus();
		obj2[10] = ac.getUrl();
		obj2[11] = res2;
		res.add(obj2);

		return res;
	}

	public static List<Object[]> Add(Ac ac,String key) throws Exception {
		if (key == null || key.equals("")) {
			throw new KeyEmptyError();
		}
		List<Object[]> resLst = new ArrayList<Object[]>();
		String res = "insert into ac(UUID,uid,groupId,title,eMail,jyUUID,summary,createTime,updateTime,status,url) values(?,?,?,?,?,?,?,?,?,?,?)";
		Object[] obj = new Object[12];
		obj[0] = ac.getUUID();
		obj[1] = Aes.Encrypt(key, ac.getUid());
		obj[2] = ac.getGroupId();
		obj[3] = Aes.Encrypt(key, ac.getTitle());
		obj[4] = Aes.Encrypt(key, ac.geteMail());
		obj[5] = java.util.UUID.randomUUID().toString().replace("-", "");
		obj[6] = Aes.Encrypt(key, ac.getSummary());
		obj[7] = (new Date()).getTime();
		obj[8] = (new Date()).getTime();
		obj[9] = ac.getStatus();
		obj[10] = Aes.Encrypt(key, ac.getUrl());
		obj[11] = res;
		resLst.add(obj);
		Object[] obj2 = new Object[3];
		obj2[0] = "1";
		obj2[1] = "syncflag";
		obj2[2] = "update quickSave set pvalue=? where pkey=?";
		resLst.add(obj2);
		return resLst;
	}

	public static Object[] CleanData() {
		List<Object> objs = new ArrayList<Object>();
		String res = "delete from ac ";
		objs.add(res);
		return objs.toArray();
	}

	public static List<Object[]> Update(Ac ac,String key) throws Exception {
		if (key == null || key.equals("")) {
			throw new KeyEmptyError();
		}
		List<Object[]> resLst = new ArrayList<Object[]>();
		List<Object> objs = new ArrayList<Object>();
		String res = "update ac set ";
		if (ac.getGroupId() != null) {
			res += "groupId=?,";
			objs.add(ac.getGroupId());
		}
		if (ac.getTitle() != null) {
			res += "title=?,";
			objs.add(Aes.Encrypt(key, ac.getTitle()));
		}
		if (ac.geteMail() != null) {
			res += "eMail=?,";
			objs.add(Aes.Encrypt(key, ac.geteMail()));
		}
		res += "jyUUID=jyUUID||'|"
				+ java.util.UUID.randomUUID().toString().replace("-", "")
				+ "',";
		if (ac.getSummary() != null) {
			res += "summary=?,";
			objs.add(Aes.Encrypt(key, ac.getSummary()));
		}
		if (ac.getUrl() != null) {
			res += "url=?,";
			objs.add(Aes.Encrypt(key, ac.getUrl()));
		}
		if (ac.getUid() != null) {
			res += "uid=?,";
			objs.add(Aes.Encrypt(key, ac.getUid()));
		}
		if (ac.getStatus() != -99) {
			res += "status=?,";
			objs.add(ac.getStatus());
		}
		res += "updateTime=? where UUID=?";
		objs.add((new Date()).getTime());
		objs.add(ac.getUUID());
		objs.add(res);
		resLst.add(objs.toArray());
		Object[] obj2 = new Object[3];
		obj2[0] = "1";
		obj2[1] = "syncflag";
		obj2[2] = "update quickSave set pvalue=? where pkey=?";
		resLst.add(obj2);
		return resLst;
	}

	@SuppressWarnings("unchecked")
	public static List<Ac> SelectWithDecrypt(SQLiteDatabase dbms,
			String groupId, int status,String key) throws Exception {
		if (key == null || key.equals("")) {
			throw new KeyEmptyError();
		}
		List<Ac> res = Select(dbms, groupId, status);
		for (int i = 0; i < res.size(); i++) {
			Ac a = res.get(i);
			a.setUid(Aes.Decrypt(key, a.getUid()));
			if (a.geteMail() != null && !a.geteMail().equals("")) {
				a.seteMail(Aes.Decrypt(key, a.geteMail()));
			}
			a.setTitle(Aes.Decrypt(key, a.getTitle()));
			if (a.getSummary() != null && !a.getSummary().equals("")) {
				a.setSummary(Aes.Decrypt(key, a.getSummary()));
			}
			if (a.getUrl() != null && !a.getUrl().equals("")) {
				a.setUrl(Aes.Decrypt(key, a.getUrl()));
			}
		}
		Collections.sort(res, new AcComparator());
		return res;
	}

	public static List<Ac> Select(SQLiteDatabase dbms, String groupId,
			int status) {
		List<Ac> res = new ArrayList<Ac>();
		String selStr = "select UUID,uid,groupId,title,eMail,jyUUID,summary,createTime,updateTime,status,url from ac where 1=1";

		if (groupId != null) {
			selStr += " and groupId=?";
		}
		if (status == 1) {
			selStr += " and status=1";
		} else if (status == 0) {
			selStr += " and status in(0,1)";
		}
		Cursor cursor = dbms.rawQuery(selStr,
				groupId != null ? new String[] { groupId } : new String[] {});
		while (cursor.moveToNext()) {
			Ac ac = new Ac();
			ac.setUUID(cursor.getString(0));
			ac.setUid(cursor.getString(1));
			ac.setGroupId(cursor.getString(2));
			ac.setTitle(cursor.getString(3));
			ac.seteMail(cursor.getString(4));
			ac.setJyUUID(cursor.getString(5));
			ac.setSummary(cursor.getString(6));
			ac.setCreateTime(new Date(cursor.getLong(7)));
			ac.setUpdateTime(new Date(cursor.getLong(8)));
			ac.setStatus(cursor.getInt(9));
			ac.setUrl(cursor.getString(10));
			res.add(ac);
		}
		return res;
	}

	public static Ac getWithDecrypt(SQLiteDatabase dbms, String uuid,String key)
			throws Exception {
		if (key == null || key.equals("")) {
			throw new KeyEmptyError();
		}
		Ac a = get(dbms, uuid);
		if (a == null) {
			return null;
		}
		a.setUid(Aes.Decrypt(key, a.getUid()));
		if (a.geteMail() != null && !a.geteMail().equals("")) {
			a.seteMail(Aes.Decrypt(key, a.geteMail()));
		}
		a.setTitle(Aes.Decrypt(key, a.getTitle()));
		if (a.getSummary() != null && !a.getSummary().equals("")) {
			a.setSummary(Aes.Decrypt(key, a.getSummary()));
		}
		if (a.getUrl() != null && !a.getUrl().equals("")) {
			a.setUrl(Aes.Decrypt(key, a.getUrl()));
		}
		return a;
	}

	public static Ac get(SQLiteDatabase dbms, String uuid) {
		String selStr = "select UUID,uid,groupId,title,eMail,jyUUID,summary,createTime,updateTime,status,url from ac where UUID=?";
		Cursor cursor = dbms.rawQuery(selStr, new String[] { uuid });
		if (cursor.moveToNext()) {
			Ac ac = new Ac();
			ac.setUUID(cursor.getString(0));
			ac.setUid(cursor.getString(1));
			ac.setGroupId(cursor.getString(2));
			ac.setTitle(cursor.getString(3));
			ac.seteMail(cursor.getString(4));
			ac.setJyUUID(cursor.getString(5));
			ac.setSummary(cursor.getString(6));
			ac.setCreateTime(new Date(cursor.getLong(7)));
			ac.setUpdateTime(new Date(cursor.getLong(8)));
			ac.setStatus(cursor.getInt(9));
			ac.setUrl(cursor.getString(10));
			return ac;
		}
		return null;
	}

}
